package com.nyist.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.nyist.pojo.User;
public class ExcelUtil {  
	private XSSFWorkbook wb = null;  
	  
    private XSSFSheet sheet = null;  
  
    /** 
     * @param wb 
     * @param sheet 
     */  
    public ExcelUtil(XSSFWorkbook wb, XSSFSheet sheet)  
    {  
        this.wb = wb;  
        this.sheet = sheet;  
    }  
  
    /** 
     * 合并单元格后给合并后的单元格加边框 
     *  
     * @param region 
     * @param cs 
     */  
    public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)  
    {  
  
        int toprowNum = region.getFirstRow();  
        for (int i = toprowNum; i <= region.getLastRow(); i++)  
        {  
            XSSFRow row = sheet.getRow(i);  
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)  
            {  
                XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,  
                                                // (short) j);  
                cell.setCellStyle(cs);  
            }  
        }  
    }  
  
    /** 
     * 设置表头的单元格样式 
     *  
     * @return 
     */  
    public XSSFCellStyle getHeadStyle()  
    {  
        // 创建单元格样式  
        XSSFCellStyle cellStyle = wb.createCellStyle();  
        // 设置单元格的背景颜色为淡蓝色  
        cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);  
        // 设置单元格居中对齐  
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
        // 设置单元格垂直居中对齐  
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
        // 创建单元格内容显示不下时自动换行  
        cellStyle.setWrapText(true);  
        // 设置单元格字体样式  
        XSSFFont font = wb.createFont();  
        // 设置字体加粗  
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  
        font.setFontName("宋体");  
        font.setFontHeight((short) 200);  
        cellStyle.setFont(font);  
        // 设置单元格边框为细线条  
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
        return cellStyle;  
    }  
  
    
    
    /** 
     * 设置表体的单元格样式 
     *  
     * @return 
     */  
    public XSSFCellStyle getBodyStyle()  
    {  
        // 创建单元格样式  
        XSSFCellStyle cellStyle = wb.createCellStyle();  
        // 设置单元格居中对齐  
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
        // 设置单元格垂直居中对齐  
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
        // 创建单元格内容显示不下时自动换行  
        cellStyle.setWrapText(true);  
        // 设置单元格字体样式  
        XSSFFont font = wb.createFont();  
        // 设置字体加粗  
        //font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  
        font.setFontName("微软雅黑");  
        font.setFontHeight((short) 200);  
        cellStyle.setFont(font);  
        // 设置单元格边框为细线条  
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
        return cellStyle;  
    }  
    
    
    /** 
     * 对外提供读取excel 的方法 
     * */  
    public static List<List<Object>> readExcel(String fileName,InputStream in) throws IOException {  
       
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName  
                .substring(fileName.lastIndexOf(".") + 1);  
        if ("xls".equals(extension)) {  
            return read2003Excel(in);  
        } else if ("xlsx".equals(extension)) {  
            return read2007Excel(in);  
        } else {  
            throw new IOException("不支持的文件类型");  
        }  
    }  
    
    /** 
     * 读取 office 2003 excel 
     * @throws IOException 
     * @throws FileNotFoundException 
     */  
    private static List<List<Object>> read2003Excel(InputStream in)  
            throws IOException {  
        List<List<Object>> list = new LinkedList<List<Object>>();  
        HSSFWorkbook hwb = new HSSFWorkbook(in);  
        HSSFSheet sheet = hwb.getSheetAt(0);  
        Object value = null;  
        HSSFRow row = null;  
        HSSFCell cell = null;  
        int counter = 0;  
        for (int i = sheet.getFirstRowNum(); counter < sheet  
                .getPhysicalNumberOfRows(); i++) {  
            row = sheet.getRow(i);  
            if (row == null) {  
                continue;  
            } else {  
                counter++;  
            }  
            List<Object> linked = new LinkedList<Object>();  
            for (int j = 0; j <= row.getLastCellNum(); j++) {  
                cell = row.getCell(j); 
                
                if (cell == null) {  
                	value= "";
                	linked.add(value);
                    continue;  
                }  
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符  
                SimpleDateFormat sdf = new SimpleDateFormat(  
                        "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串  
                DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字  
                switch (cell.getCellType()) {  
                case XSSFCell.CELL_TYPE_STRING:  
                    //System.out.println(i + "行" + j + " 列 is String type");  
                    value = cell.getStringCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_NUMERIC:  
                	 if ("@".equals(cell.getCellStyle().getDataFormatString())) {  
                         value = df.format(cell.getNumericCellValue()); 
                         System.out.println(i + "行" + j  
                                 + " 列 is Number type ; @"  
                                 + value); 
                     } else if ("General".equals(cell.getCellStyle()  
                             .getDataFormatString())) {  
                         value = nf.format(cell.getNumericCellValue());  
                         System.out.println(i + "行" + j  
                                 + " 列 is Number type ; General"  
                                 + value); 
                     } else { 
                     	BigDecimal decimal = new BigDecimal(cell.getNumericCellValue());
                         value = decimal.toString();
                         System.out.println(i + "行" + j  
                                 + " 列 is Number type ; "  
                                 + value); 
                     } 
                	 break;
                case XSSFCell.CELL_TYPE_BOOLEAN:  
                    //System.out.println(i + "行" + j + " 列 is Boolean type");  
                    value = cell.getBooleanCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_BLANK:  
                    //System.out.println(i + "行" + j + " 列 is Blank type");  
                    value = "";  
                    break;  
                default:  
                    //System.out.println(i + "行" + j + " 列 is default type");  
                    value = cell.toString();  
                }  
                if (value == null || "".equals(value)) {  
                   // continue;  
                	value="";
                }  
                linked.add(value);  
            }  
            list.add(linked);  
        }  
        return list;  
    }  
    /** 
     * 读取Office 2007 excel 
     * */  
    private static List<List<Object>> read2007Excel(InputStream in)  
            throws IOException {  
        List<List<Object>> list = new LinkedList<List<Object>>();  
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径  
        XSSFWorkbook xwb = new XSSFWorkbook(in);  
        // 读取第一章表格内容  
        XSSFSheet sheet = xwb.getSheetAt(0);  
        Object value = null;  
        XSSFRow row = null;  
        XSSFCell cell = null;  
        int counter = 0;  
        for (int i = sheet.getFirstRowNum(); counter < sheet  
                .getPhysicalNumberOfRows(); i++) {  
            row = sheet.getRow(i);  
            if (row == null) {  
                continue;  
            } else {  
                counter++;  
            }  
            List<Object> linked = new LinkedList<Object>();  
            for (int j = 0; j <= row.getLastCellNum(); j++) {  
                cell = row.getCell(j);  
                if (cell == null) { 
                	value="";
                	linked.add(value);  
                    continue;  
                }  
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符  
                SimpleDateFormat sdf = new SimpleDateFormat(  
                        "yyyy-MM-dd HH:mm:ss");// 格式化日期字符串  
                DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字  
                switch (cell.getCellType()) {  
                case XSSFCell.CELL_TYPE_STRING:  
                    System.out.println(i + "行" + j + " 列 is String type :" + cell.getStringCellValue());  
                    value = cell.getStringCellValue(); 
                    
                    break;  
                case XSSFCell.CELL_TYPE_NUMERIC:  
                     
                    if ("@".equals(cell.getCellStyle().getDataFormatString())) {  
                        value = df.format(cell.getNumericCellValue()); 
                        System.out.println(i + "行" + j  
                                + " 列 is Number type ; @"  
                                + value); 
                    } else if ("General".equals(cell.getCellStyle()  
                            .getDataFormatString())) {  
                        value = nf.format(cell.getNumericCellValue());  
                        System.out.println(i + "行" + j  
                                + " 列 is Number type ; General"  
                                + value); 
                    } else { 
                    	BigDecimal decimal = new BigDecimal(cell.getNumericCellValue());
                        value = decimal.toString();
                        System.out.println(i + "行" + j  
                                + " 列 is Number type ; "  
                                + value); 
                    } 
                    
                    break;  
                case XSSFCell.CELL_TYPE_BOOLEAN:  
                    System.out.println(i + "行" + j + " 列 is Boolean type");  
                    value = cell.getBooleanCellValue();  
                    break;  
                case XSSFCell.CELL_TYPE_BLANK:  
                    System.out.println(i + "行" + j + " 列 is Blank type");  
                    value = "";  
                    break;  
                default:  
                    System.out.println(i + "行" + j + " 列 is default type");  
                    value = cell.toString();  
                }  
                if (value == null || "".equals(value)) {  
                    //continue;  
                	value="";
                }  
                linked.add(value);  
            }  
            list.add(linked);  
        }  
        return list;  
    }  
    public static void main(String[] args) {  
        try {  
        	InputStream is = new FileInputStream("C:\\Users\\hu\\Desktop\\用户表.xlsx");
            List<List<Object>> list = ExcelUtil.readExcel("用户表.xlsx", is);
            for (List<Object> list2 : list) {
				System.out.println(list2);
			}
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
    }  
  
    
    public static HSSFCell createCellAndSetValue(HSSFRow newrow, int colNo,
			String value, HSSFCellStyle cellStyle) {
		HSSFCell cell = newrow.createCell(colNo);
		cell.setCellStyle(cellStyle);
		cell.setCellValue(new HSSFRichTextString(value));
		return cell;
	}
} 
